﻿Imports System.Data.SqlClient
Public Class User
    Dim cmd As New SqlCommand
#Region "select data from SQl server"
    Public Function gettable() As DataTable
        Try
            Dim dad As SqlDataAdapter = New SqlDataAdapter("select *from tblUser", cn)
            Dim dtl As DataTable = New DataTable("Resturant")
            dad.Fill(dtl)
            Return dtl
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        End Try
    End Function
#End Region

#Region "Connection"
    Private Sub User_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        condb()
        Me.DataGridView1.DataSource = gettable()
        Main.lblheader.Text = "គណនីអ្នកប្រើប្រាស់"
    End Sub
#End Region

#Region "Insert data"

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
        Try
            cmd = New SqlCommand
            cmd.Connection = cn
            cmd.CommandText = "INSERT INTO tblUser VALUES (N'" & txtusername.Text & "',N'" & txtPassword.Text & "',N'" & txtusertype.Text & "')"
            cmd.ExecuteNonQuery()
            DataGridView1.DataSource = gettable()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
#End Region

#Region "Update"
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            cmd = New SqlCommand
            cmd.Connection = cn
            cmd.CommandText = "update TblUser set UserName=N'" & txtusername.Text & "',Password=N'" & txtPassword.Text & "',Usertype=N'" & txtusertype.Text & "' where UserID='" & txtid.Text & "';"
            cmd.ExecuteNonQuery()
            MessageBox.Show("Update Succes")
            Me.DataGridView1.DataSource = gettable()
        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Sub

    Public Function Beforupdate() As Boolean
        txtid.Text = DataGridView1.CurrentRow.Cells("UserID").Value
        txtusername.Text = DataGridView1.CurrentRow.Cells("UserName").Value
        txtPassword.Text = DataGridView1.CurrentRow.Cells("Password").Value
        txtusertype.Text = DataGridView1.CurrentRow.Cells("UserType").Value

        Return True
    End Function

    Private Sub DataGridView1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.SelectionChanged
        Try
            Beforupdate()
        Catch ex As Exception

        End Try

    End Sub
#End Region

#Region "Delete data"
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try
            cmd = New SqlCommand
            cmd.Connection = cn
            cmd.CommandText = "Delete tblUser where UserID='" & txtid.Text & "';"
            cmd.ExecuteNonQuery()
            Me.DataGridView1.DataSource = gettable()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
#End Region

#Region "Clear"

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        txtid.Clear()
        txtPassword.Clear()
        txtusername.Clear()

    End Sub
#End Region

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub
End Class